﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.ObjectModel;
using System.Text;
using PIMS.XmlModel;
using System.Data;
using PIMS.Utils;
using PIMS_Tools;
using PIMS.WorkPlugin.Util.Config;

namespace PIMS.WorkPlugin.ORM
{
    /// <summary>
    /// 参考：
    /// https://blog.csdn.net/Zzu_zzx/article/details/125988334
    /// </summary>
    internal class SqlSugarHelperExternal
    {
        #region 初始化数据库类型及连接字符串
        /*
          <appSettings>
          <!-- DB -->    
          <add key="dbType" value="MySql" />
          <add key="connStr" value="Database='SqlSugarTest';Data Source='127.0.0.1';User Id='root';Password='eman';charset='utf8';pooling=true" />
          </appSettings>
         * 
          <appSettings>
          <!-- DB -->    
          <add key="dbType" value="SqlServer" />
          <add key="connStr" value="Server=127.0.0.1;Database=SqlSugarTest;Uid=sa;Pwd=sa" />    
          </appSettings>
        */
        /// <summary>
        ///  可用的类型：MySql,SqlServer,Sqlite,Oracle,PostgreSQL,Dm,Kdbndp
        /// </summary>
        private static string dbTypeStr = ConfigManager.AppSettings["dbType"].Value;
        /// <summary>
        /// 数据库连接字符串设置
        /// </summary>
        private static string connStr = ConfigManager.AppSettings["connStr"].Value;

        #endregion

        /// <summary>
        /// 获取程序数据库操作对象
        /// </summary>
        /// <param name="strConn">外部给定数据库连接字符串</param>
        /// <returns></returns>
        public static SqlSugarClient GetDb(string dbTypeString, string connString)
        {
            var db = new SqlSugarClient(
                new ConnectionConfig()
                {
                    ConnectionString = connString,
                    DbType = (SqlSugar.DbType)Enum.Parse(typeof(SqlSugar.DbType), dbTypeString),
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute,
                    AopEvents = new AopEvents
                    {
                        OnLogExecuting = (sql, p) =>
                        {
                            Console.WriteLine(sql);
                            if (p != null)
                                Console.WriteLine(string.Join(",", p.Select(it => it.ParameterName + ":" + it.Value)));
                        }
                    }
                });
            return db;
        }
        

        public static SqlSugarClient GetDb()
        {
            return GetDb(dbTypeStr,connStr);
        }

        /// <summary>
        /// DBFirst
        /// 根据数据库现有表自动生成实体类
        /// </summary>
        public static void GenerateEntity()
        {
            try
            {
                var db = GetDb();
                db.Ado.CheckConnection();
                var path = AppDomain.CurrentDomain.BaseDirectory + "\\Entity";//生成的实体存入的文件夹路径
                if (!Directory.Exists(path)) Directory.CreateDirectory(path);
                db.DbFirst.CreateClassFile(path, "SqlSugarEntity");

                Console.WriteLine("***********************OK*****************************");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        /// <summary>
        /// CodeFirst
        /// 根据实体自动创建数据库
        /// 慎用!!!
        /// 会删除现有数据库的数据
        /// </summary>
        public static void CodeFirst()
        {
            try
            {
                var sqlSugarClient = GetDb();

                #region 创建数据库和表的语句仅执行一次
                // sqlSugarClient.DbMaintenance.CreateDatabase(); //创建数据库
                // sqlSugarClient.CodeFirst.SetStringDefaultLength(50).InitTables(typeof(Tb_User));//创建表 varchar[200]
                #endregion

                sqlSugarClient.Aop.OnLogExecuted = (sql, pra) =>
                {
                    Console.WriteLine("*****************************************************");
                    Console.WriteLine(string.Format("sql语句:{0}", sql));
                };


                //新增一条记录
                //sqlSugarClient.Insertable<Tb_User>(new Tb_User()
                //{
                //    F_UserId = 1,
                //    F_UaerName = "测试",
                //    F_UserEmail = "test@163.com",
                //    F_Password = "test",
                //    F_UserPower = "testtest",
                //    F_UserArea= "123"
                //}).ExecuteCommand();

                //user.F_UserPower = "132";
                //sqlSugarClient.Updateable<Tb_User>(user).ExecuteCommand();//修改

                // sqlSugarClient.Deleteable<Tb_User>(user).ExecuteCommand();//删除

                //var getSingleOrDefault = sqlSugarClient.Queryable<Tb_User>().Where(A => A.F_UserId == 2).Single();
                //if (getSingleOrDefault != null)
                //{
                //    System.Windows.Forms.MessageBox.Show(getSingleOrDefault.F_UaerName);
                //}

            }
            catch (Exception)
            {
                throw;
            }
        }

        #region Query
        /// <summary>
        /// 查询所有的用户记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<T> QueryList<T>() where T : class
        {
            try
            {
                return GetDb().Queryable<T>().ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 指定SQL查询语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdSql"></param>
        /// <returns></returns>
        public static List<T> Query<T>(string cmdSql) where T : class, new()
        {
            try
            {
                return GetDb().SqlQueryable<T>(cmdSql).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<T> Queryy<T>(string conType,string connString, string cmdSql) where T : class, new()
        {
            try
            {
                return GetDb(conType,connString).SqlQueryable<T>(cmdSql).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 条件查询带排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">查询条件</param>
        /// <param name="top">符合条件的前N条记录</param>
        /// <param name="orderBy">排序字段名</param>
        /// <returns></returns>
        public static List<T> QueryList<T>(string where, int top = 0, string orderBy = "")
        {
            try
            {
                if (String.IsNullOrEmpty(orderBy))
                {
                    if (top == 0)
                    {
                        return GetDb().Queryable<T>()
                         .WhereIF(where != "", where)
                         .ToList();
                    }
                    else
                    {
                        return GetDb().Queryable<T>()
                         .WhereIF(where != "", where)
                         .Take(top)
                         .ToList();
                    }
                }
                else
                {
                    if (top == 0)
                    {
                        return GetDb().Queryable<T>()
                         .WhereIF(where != "", where)
                         .OrderBy(orderBy)
                         .ToList();
                    }
                    else
                    {
                        return GetDb().Queryable<T>()
                         .WhereIF(where != "", where)
                         .OrderBy(orderBy)
                         .Take(top)
                         .ToList();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询符合条件的第一条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static T Query<T>(string where, string orderBy)
        {
            try
            {
                if (String.IsNullOrEmpty(orderBy))
                {
                    return GetDb().Queryable<T>().Where(where).First();
                }
                else
                {
                    return GetDb().Queryable<T>().Where(where).OrderBy(orderBy).First();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据主键进行查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pkValue"></param>
        /// <returns></returns>
        public static T QueryByKey<T>(object pkValue)
        {
            try
            {
                return GetDb().Queryable<T>().InSingle(pkValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static T Query<T>(string conType,string connString, object pkValue)
        {
            try
            {
                return GetDb(conType,connString).Queryable<T>().InSingle(pkValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据SQL查询串查询得到DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdSql"></param>
        /// <returns></returns>
        public static DataTable QueryToDataTable<T>(string cmdSql) where T : class, new()
        {
            try
            {
                return GetDb().SqlQueryable<T>(cmdSql).ToDataTable();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static T QueryOrderByDesc<T>(Expression<Func<T, bool>> lambdaWhere, Expression<Func<T, object>> lambdaExpression)
        {
            try
            {
                if (lambdaExpression == null)
                {
                    return GetDb().Queryable<T>().Where(lambdaWhere).First();
                }

                return GetDb().Queryable<T>().Where(lambdaWhere).OrderByDescending(lambdaExpression)
                    .First();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Insert
        /// <summary>
        /// 插入数据库记录
        /// 说明：对于自增长列的表插入数据后，当前自增长列的字段，仍旧为0
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int InsertRecord<T>(T model) where T : class, new()
        {
            try
            {
                return GetDb().Insertable<T>(model).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 插入数据库记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="connString">外部连接字符串</param>
        /// <returns></returns>
        public static int InsertRecord<T>(string conType, string connString,T model) where T : class, new()
        {
            try
            {
                return GetDb(conType,connString).Insertable<T>(model).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 插入多条数据库记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int InsertRecord<T>(List<T> listModel) where T : class, new()
        {
            try
            {
                return GetDb().Insertable<T>(listModel).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Update
        /// <summary>
        /// 更新符合条件的用户记录的用户名
        /// 只更新一个字段
        /// </summary>
        /// <param name="model"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public static int UpdateRecord<T>(string where, string colName, string newValue) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb().Updateable<T>()
                     .Where(where)
                     .SetColumns(p => colName == newValue)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }

        /// <summary>
        /// 更新符合条件的记录的多个字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="columnDictionary"></param>
        /// <returns></returns>
        public static int UpdateRecord<T>(string where, Dictionary<string, object> columnDictionary) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb().Updateable<T>(columnDictionary)
                     .Where(where)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }

        /// <summary>
        /// 更新整个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static int UpdateRecord<T>(T model) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb().Updateable<T>(model)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }

        public static int UpdateRecord<T>(string conType,string connString,T model) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb(conType,connString).Updateable<T>(model)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除数据库记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static int DeleteRecord<T>(string where) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb().Deleteable<T>()
                     .Where(where)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }

        /// <summary>
        /// 根据主键进行删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pkValue"></param>
        /// <returns></returns>
        public static int DeleteRecordByKey<T>(dynamic primaryKeyValue) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb().Deleteable<T>(primaryKeyValue)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }

        public static int DeleteRecord<T>(string conType, string connString, dynamic primaryKeyValue) where T : class, new()
        {
            int resultCount = 0;
            try
            {
                resultCount = GetDb(conType,connString).Deleteable<T>(primaryKeyValue)
                     .ExecuteCommand();
            }
            catch (Exception e)
            {
                return -1;
            }
            return resultCount;
        }
        #endregion

        /// <summary>
        /// SqlSugar通用的执行存储过程方法（以out方式返回参数)
        /// </summary>
        /// <param name="sprocName"></param>
        /// <param name="intParams"></param>
        /// <param name="outParams"></param>
        public static void ExecuteProcedure(string sprocName, Dictionary<string, object> intParams, Dictionary<string, object> outParams)
        {
            List<SugarParameter> pList = new List<SugarParameter>();
            if (intParams != null)
            {
                pList = intParams.Select(
                    obj => new SugarParameter($@"@{obj.Key}", obj.Value))
                    .ToList();            }
            if (outParams != null)
                pList.AddRange(
                    outParams.Select(
                        obj => new SugarParameter($@"@{obj.Key}", obj.Value)
                        {
                            Direction = ParameterDirection.Output
                        }));
            GetDb().Ado.UseStoredProcedure().ExecuteCommand(sprocName, pList);
            foreach (var p in pList.Where(r => r.Direction == ParameterDirection.Output))
            {
                var pName = p.ParameterName.Substring(1);
                if (outParams != null && outParams.ContainsKey(pName))
                {
                    outParams[pName] = p.Value;
                }
            }
        }
    }
}
